/**
  * Copyright (c) CSIRO Australia, 2009
  *
  * @author $Author: jia020 $
  * @version $Id: MySQL.java 643 2009-05-20 00:20:55Z jia020 $
  */
package au.csiro.nt.pdsp.util;

import java.sql.*;
import java.sql.ResultSet;

/**
 *mySQL
 * 
 * @author jia020
 */
public class MySQL {
	private String m_sSqlUser;
	private String m_sSqlPin;
	private String m_sSqlUrl;
	private Connection m_connSql;
	private Statement m_statSql;
	private ResultSet m_recordset;
	private int m_iCount;

	public MySQL(String sUser, String sPin, String sUrl) {
		m_sSqlUser = sUser;
		m_sSqlPin = sPin;
		m_sSqlUrl = sUrl;
		m_iCount = 0;
		boolean bRet = init();
		if (!bRet)
			System.err.println("error:init.");

	}	

	public Connection getConnection() {
		return m_connSql;
	}
	public ResultSet execSelect(String sSql) {
		m_iCount = 0;
		do {
			try {
				m_statSql.executeQuery(sSql);
				ResultSet recordset = m_statSql.getResultSet();
				if (recordset.wasNull())
					return null;
				else
					return recordset;
			} catch (Exception e) {
				System.err.println(e);
				boolean bInit = init();
				m_iCount++;
				if (m_iCount > 5)
					return null;
			}
		} while (true);
	}

	public boolean execInsert(String sSql) {
		m_iCount = 0;
		do {
			try {
				m_statSql.execute(sSql);
				return true;
			} catch (Exception e) {
				System.err.println(e);
				boolean bInit = init();
				m_iCount++;
				if (m_iCount > 5)
					return false;
			}
		} while (true);

	}

	public boolean execUpdate(String sSql) {
		m_iCount = 0;
		do {
			try {
				m_statSql.executeUpdate(sSql);
				return true;
			} catch (Exception e) {
				System.err.println(e);
				boolean bInit = init();
				m_iCount++;
				if (m_iCount > 5)
					return false;
			}
		} while (true);
	}

	private boolean init() {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			m_connSql = DriverManager.getConnection(m_sSqlUrl, m_sSqlUser, m_sSqlPin);
			m_statSql = m_connSql.createStatement();
			return true;
		} catch (Exception e) {
			System.err.println("error:convertFromObj.");
			e.printStackTrace();
			return false;
		}
	}

	public static void main(String[] args) {
		int nEContractID = 0;
		try {
			MySQL mysql = new MySQL("root", "Centie2nat",
					"jdbc:mysql://127.0.0.1:3306/econtract?autoReconnect=true");
			String sSql = "select eContractID from econtractmanager";
			while (true) {
				ResultSet rsSearch = mysql.execSelect(sSql);
				if (rsSearch == null)
					return;

				while (rsSearch.next()) {
					nEContractID = rsSearch.getInt("eContractID");
					System.out.print(Integer.toString(nEContractID));
				}
				Thread.sleep(10000);
			}
		} catch (Exception ex) {
			System.out.print(ex.toString());
		}
	}

}
